package org.petstore.persistence.impl;

import org.petstore.domain.Order;
import org.petstore.persistence.OrderDAO;
import org.petstore.persistence.util.JDBCUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.Date;
import java.util.List;

public class OrderDAOImpl implements OrderDAO {
    private final JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
    private static final String INSERT_ORDER = "INSERT INTO ORDERS (ORDERID, USERID, ORDERDATE, SHIPADDR1, SHIPADDR2, SHIPCITY, SHIPSTATE," +
            "      SHIPZIP, SHIPCOUNTRY, BILLADDR1, BILLADDR2, BILLCITY, BILLSTATE, BILLZIP, BILLCOUNTRY," +
            "      COURIER, TOTALPRICE, BILLTOFIRSTNAME, BILLTOLASTNAME, SHIPTOFIRSTNAME, SHIPTOLASTNAME," +
            "      CREDITCARD, EXPRDATE, CARDTYPE, LOCALE)" +
            "      VALUES(?, ?, ?, ?, ?" +
            "      , ?," +
            "      ?,?, ?, ?, ?," +
            "      ?, ?, ?, ?,?," +
            "      ?, ?," +
            "      ?, ?, ?, ?, ?," +
            "      ?,?)";
    private static final String INSERT_ORDERSTATUS = "INSERT INTO ORDERSTATUS (ORDERID, LINENUM, TIMESTAMP, STATUS)" +
            "    VALUES (?, ?, ?, ?)";
    private static final String GET_ORDER = "SELECT" +
            "      BILLADDR1 AS billAddress1," +
            "      BILLADDR2 AS billAddress2," +
            "      BILLCITY," +
            "      BILLCOUNTRY," +
            "      BILLSTATE," +
            "      BILLTOFIRSTNAME," +
            "      BILLTOLASTNAME," +
            "      BILLZIP," +
            "      SHIPADDR1 AS shipAddress1," +
            "      SHIPADDR2 AS shipAddress2," +
            "      SHIPCITY," +
            "      SHIPCOUNTRY," +
            "      SHIPSTATE," +
            "      SHIPTOFIRSTNAME," +
            "      SHIPTOLASTNAME," +
            "      SHIPZIP," +
            "      CARDTYPE," +
            "      COURIER," +
            "      CREDITCARD," +
            "      EXPRDATE AS expiryDate," +
            "      LOCALE," +
            "      ORDERDATE," +
            "      ORDERS.ORDERID," +
            "      TOTALPRICE," +
            "      USERID AS username," +
            "      STATUS" +
            "    FROM ORDERS, ORDERSTATUS" +
            "    WHERE ORDERS.ORDERID = ?" +
            "      AND ORDERS.ORDERID = ORDERSTATUS.ORDERID";
    private static final String GET_ORDER_BY_USERNAME = "SELECT" +
            "      BILLADDR1 AS billAddress1," +
            "      BILLADDR2 AS billAddress2," +
            "      BILLCITY," +
            "      BILLCOUNTRY," +
            "      BILLSTATE," +
            "      BILLTOFIRSTNAME," +
            "      BILLTOLASTNAME," +
            "      BILLZIP," +
            "      SHIPADDR1 AS shipAddress1," +
            "      SHIPADDR2 AS shipAddress2," +
            "      SHIPCITY," +
            "      SHIPCOUNTRY," +
            "      SHIPSTATE," +
            "      SHIPTOFIRSTNAME," +
            "      SHIPTOLASTNAME," +
            "      SHIPZIP," +
            "      CARDTYPE," +
            "      COURIER," +
            "      CREDITCARD," +
            "      EXPRDATE AS expiryDate," +
            "      LOCALE," +
            "      ORDERDATE," +
            "      ORDERS.ORDERID," +
            "      TOTALPRICE," +
            "      USERID AS username," +
            "      STATUS" +
            "    FROM ORDERS, ORDERSTATUS" +
            "    WHERE ORDERS.USERID = ?" +
            "      AND ORDERS.ORDERID = ORDERSTATUS.ORDERID" +
            "    ORDER BY ORDERDATE";

    /**
     * description 通过username获取该用户的所有订单
     *
     * @param username:
     * @return List<Order>
     * @author huangzhangyan
     * @date 2021/11/1 0:40
     */
    @Override
    public List<Order> getOrdersByUsername(String username) {
        List<Order> list;
        try {
            list = template.query(GET_ORDER_BY_USERNAME, new BeanPropertyRowMapper<>(Order.class), username);
            for (Order order : list) {
                System.out.println(order.getOrderId());
            }
            //todo 调试
            System.out.println(list.size());
            System.out.println("return orders");
            if (list.size() == 0) {
                return null;
            }
            return list;
        } catch (DataAccessException e) {
            list = null;
            return list;
        }
    }

    /**
     * description 通过orderID获取order
     *
     * @param orderId:
     * @return Order
     * @author huangzhangyan
     * @date 2021/11/1 0:40
     */
    @Override
    public Order getOrderByOrderId(int orderId) {
        Order order;
        try {
            order = template.queryForObject(GET_ORDER, new BeanPropertyRowMapper<>(Order.class), orderId);
            return order;
        } catch (DataAccessException e) {
            return null;
        }
    }

    /**
     * description 插入一个新的订单,在orders表中
     *
     * @param order:
     * @author huangzhangyan
     * @date 2021/11/1 0:44
     */
    @Override
    public void insertOrder(Order order) {
        template.update(INSERT_ORDER, order.getOrderId(), order.getUsername(), order.getOrderDate(), order.getShipAddress1(), order.getShipAddress2()
                , order.getShipCity(), order.getShipState(), order.getShipZip(), order.getShipCountry(), order.getBillAddress1(), order.getBillAddress2(), order.getBillCity()
                , order.getBillState(), order.getBillZip(), order.getBillCountry(), order.getCourier(), order.getTotalPrice(), order.getBillToFirstName()
                , order.getBillToLastName(), order.getShipToFirstName(), order.getShipToLastName(), order.getCreditCard(), order.getExpiryDate(), order.getCardType()
                , order.getLocale());
    }

    /**
     * description 插入一个新的订单状态,在orderstatus表中
     *
     * @param order:
     * @author huangzhangyan
     * @date 2021/11/1 0:44
     */
    @Override
    public void insertOrderStatus(Order order) {
        template.update(INSERT_ORDERSTATUS, order.getOrderId(), order.getLineItems().size(), new Date(order.getOrderDate().getTime()), order.getStatus());
    }
}
